package com.yunchen.BooksManagementSystem.manage.dao;

import com.yunchen.BooksManagementSystem.user.entity.UserInformation;
import com.yunchen.BooksManagementSystem.utils.DBUtil;
import com.yunchen.BooksManagementSystem.manage.entity.borrowingRecord;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;


public class adminDaoImpl implements adminDao{

    public int addBook(String picture,String name,String author,String category,int amount,String introduction) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = "insert into booksinfo (picture,bookName,author,category,number,introduction) values(?,?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,picture);
        pstmt.setString(2,name);
        pstmt.setString(3,author);
        pstmt.setString(4,category);
        pstmt.setInt(5,amount);
        pstmt.setString(6,introduction);
        int count = pstmt.executeUpdate();
        pstmt.close();
        return count;
    }

    public int delBook(String name) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "delete from booksinfo where bookName = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,name);
        int count = pstmt.executeUpdate();
        System.out.println(count);
        pstmt.close();
        return count;
    }
    public int updateBook(String name,int amount) throws Exception {
        Connection conn=DBUtil.getConnection();
        String sql="update booksinfo set number=? where bookName=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,amount);
        pstmt.setString(2,name);
        int count=pstmt.executeUpdate();
        System.out.println(count);
        pstmt.close();
        return count;
    }
    public ArrayList<UserInformation> findAllStudent() throws SQLException{
        ArrayList<UserInformation> userList=new ArrayList<>();
        Connection conn=DBUtil.getConnection();
        String sql="select * from userinfo";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs=pstmt.executeQuery();
        while (rs.next()){
            UserInformation user= new UserInformation();
            user.setUserId(rs.getString("userId"));
            user.setUserName(rs.getString("name"));
            user.setGrade(rs.getString("grade"));
            user.setDirection(rs.getString("direction"));
            userList.add(user);
        }
        System.out.println(userList);
        rs.close();
        pstmt.close();
        return userList;
    }

    public int delUser(String userName) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = "delete from userinfo where name=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        int count = pstmt.executeUpdate();
        pstmt.close();
        return count;
    }

    //同意借书
    public int agreeBorrow(String userName,String bookName) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = "update borrowingrecord set status='借书中',borrowTime=? where name=? and bookName=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        LocalDate date = LocalDate.now();
        pstmt.setDate(1,Date.valueOf(date));
        pstmt.setString(2,userName);
        pstmt.setString(3,bookName);
        int count = pstmt.executeUpdate();
        pstmt.close();
        return count;
    }
    public int agreeReturnBook(String userName,String bookName)throws SQLException{
        Connection conn=DBUtil.getConnection();
        String sql="update borrowingrecord set status='已还书',returnTime=? where name=? and bookName=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        LocalDate date = LocalDate.now();
        pstmt.setDate(1,Date.valueOf(date));
        pstmt.setString(2,userName);
        pstmt.setString(3,bookName);
        int count = pstmt.executeUpdate();
        pstmt.close();
        return count;
    }
    public ArrayList<borrowingRecord> showRecord() throws SQLException {
        ArrayList<borrowingRecord> records=new ArrayList<>();
        Connection conn=DBUtil.getConnection();
        String sql="select * from borrowingrecord";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs=pstmt.executeQuery();
        while (rs.next()){
            borrowingRecord br=new borrowingRecord();
            br.setName(rs.getString("name"));
            br.setNumber(rs.getInt("amount"));
            br.setBookName(rs.getString("bookName"));
            br.setBorrowTime(rs.getDate("borrowTime"));
            br.setReturnTime(rs.getDate("returnTime"));
            br.setStatus(rs.getString("status"));
            records.add(br);
        }
        rs.close();
        pstmt.close();
        return records;
    }
    public ArrayList<borrowingRecord> userBorrowApply()throws SQLException{
        ArrayList<borrowingRecord> userBorrowApply=new ArrayList<>();
        Connection conn=DBUtil.getConnection();
        String sql="select * from borrowingrecord where status='申请借阅'";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs=pstmt.executeQuery();
        while (rs.next()){
            borrowingRecord br=new borrowingRecord();
            br.setName(rs.getString("name"));
            br.setNumber(rs.getInt("amount"));
            br.setBookName(rs.getString("bookName"));
            br.setBorrowTime(rs.getDate("borrowTime"));
            br.setReturnTime(rs.getDate("returnTime"));
            br.setStatus(rs.getString("status"));
           userBorrowApply.add(br);
        }
        rs.close();
        pstmt.close();
        return userBorrowApply;

    }

    public ArrayList<borrowingRecord> userReturnApply() throws SQLException{
        ArrayList<borrowingRecord> userReturnApply=new ArrayList<>();
        Connection conn=DBUtil.getConnection();
        String sql="select * from borrowingrecord where status='申请还书'";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs=pstmt.executeQuery();
        while (rs.next()){
            borrowingRecord br=new borrowingRecord();
            br.setName(rs.getString("name"));
            br.setNumber(rs.getInt("amount"));
            br.setBookName(rs.getString("bookName"));
            br.setBorrowTime(rs.getDate("borrowTime"));
            br.setReturnTime(rs.getDate("returnTime"));
            br.setStatus(rs.getString("status"));
            userReturnApply.add(br);
        }
        rs.close();
        pstmt.close();
        return userReturnApply;
    }




}
